
using System;
using unoidl.com.sun.star.beans;
using unoidl.com.sun.star.container;
using unoidl.com.sun.star.frame;
using unoidl.com.sun.star.lang;
using unoidl.com.sun.star.sheet;
using unoidl.com.sun.star.table;
using unoidl.com.sun.star.text;
using unoidl.com.sun.star.uno;
using Exception = System.Exception;
using lo=unoidl.com.sun.star;

namespace ConsoleApplication1
{

  // __________  implementation  ____________________________________

  /** Create and modify a spreadsheet document.
   */
  public class SpreadsheetSample : SpreadsheetDocHelper
  {
    public static void Main(String [] args)
    {
      XComponentContext oStrap = uno.util.Bootstrap.bootstrap();
      XMultiServiceFactory oServMan = (XMultiServiceFactory) oStrap.getServiceManager();
      XComponentLoader oDesk = (XComponentLoader) oServMan.createInstance("com.sun.star.frame.Desktop");

      string url = @"private:factory/scalc";
      Uri uri = new Uri(@"c:\aaa.ods");

      var o = uri.LocalPath;
      PropertyValue[] propVals = new PropertyValue[0];
      XComponent oDoc = oDesk.loadComponentFromURL(url, "_blank", 0, propVals);
      XSpreadsheets oSheets = ( (XSpreadsheetDocument) oDoc ).getSheets();
      XIndexAccess oSheetsIA = (XIndexAccess) oSheets;
      XSpreadsheet oSheet = (XSpreadsheet) oSheetsIA.getByIndex(0).Value;

      XCell oCell = oSheet.getCellByPosition(0, 0); //A1
      ( (XText) oCell ).setString("Cost");

      oCell = oSheet.getCellByPosition(1, 0); //B1
      oCell.setValue(200);

      oCell = oSheet.getCellByPosition(1, 2); //B3
      oCell.setFormula("=B1 * 1.175");

      string fileName = @"file:///C:/vat.ods"; 
      XStorable stor = (XStorable) oDoc;
      stor.storeAsURL(fileName, propVals);
      oDoc.dispose();
      oDoc = null;      

    }

    public static void Main3(String[] args)
    {
      try
      {
        using( SpreadsheetSample aSample = new SpreadsheetSample(args) )
        {
          aSample.doSampleFunctions();
        }
        Console.WriteLine("\nSamples done.");
      }
      catch( Exception ex )
      {
        Console.WriteLine("Sample caught exception! " + ex);
      }

      Console.ReadKey();
    }

    public SpreadsheetSample(String[] args)
      : base(args)
    {
    }

    /** This sample function performs all changes on the document. */
    public void doSampleFunctions()
    {
      doCellSamples();
      doCellRangeSamples();
      doCellRangesSamples();
      doCellCursorSamples();
      doFormattingSamples();
      doDocumentSamples();
      doDatabaseSamples();
      doDataPilotSamples();
      doNamedRangesSamples();
      doFunctionAccessSamples();
      doApplicationSettingsSamples();
    }

    // ________________________________________________________________

    /** All samples regarding the service com.sun.star.sheet.SheetCell. */
    private void doCellSamples()
    {
      Console.WriteLine("\n*** Samples for service sheet.SheetCell ***\n");
      lo.sheet.XSpreadsheet xSheet = getSpreadsheet(0);
      lo.table.XCell xCell = null;
      lo.beans.XPropertySet xPropSet = null;
      String aText;
      prepareRange(xSheet, "A1:C7", "Cells and Cell Ranges");

      // --- Get cell B3 by position - (column, row) ---
      xCell = xSheet.getCellByPosition(1, 2);

      // --- Insert two text paragraphs into the cell. ---
      lo.text.XText xText = (lo.text.XText) xCell;
      lo.text.XTextCursor xTextCursor = xText.createTextCursor();

      xText.insertString(xTextCursor, "Text in first line.", false);
      xText.insertControlCharacter(xTextCursor,lo.text.ControlCharacter.PARAGRAPH_BREAK, false);
      xText.insertString(xTextCursor, "And a ", false);

      // create a hyperlink
      lo.lang.XMultiServiceFactory xServiceMan = (lo.lang.XMultiServiceFactory) getDocument();
      Object aHyperlinkObj = xServiceMan.createInstance("com.sun.star.text.TextField.URL");
      xPropSet = (lo.beans.XPropertySet) aHyperlinkObj;
      xPropSet.setPropertyValue( "URL", new uno.Any("http://www.example.org"));
      xPropSet.setPropertyValue( "Representation", new uno.Any("hyperlink"));
      // ... and insert
      lo.text.XTextContent xContent = (lo.text.XTextContent) aHyperlinkObj;
      xText.insertTextContent(xTextCursor, xContent, false);

      // --- Query the separate paragraphs. ---
      lo.container.XEnumerationAccess xParaEA = (lo.container.XEnumerationAccess) xCell;
      lo.container.XEnumeration xParaEnum = xParaEA.createEnumeration();
      // Go through the paragraphs
      while( xParaEnum.hasMoreElements() )
      {
        uno.Any aPortionObj = xParaEnum.nextElement();
        lo.container.XEnumerationAccess xPortionEA = (lo.container.XEnumerationAccess) aPortionObj.Value;
        lo.container.XEnumeration xPortionEnum = xPortionEA.createEnumeration();
        aText = "";
        // Go through all text portions of a paragraph and construct string.
        while( xPortionEnum.hasMoreElements() )
        {
          lo.text.XTextRange xRange = (lo.text.XTextRange) xPortionEnum.nextElement().Value;
          aText += xRange.getString();
        }
        Console.WriteLine("Paragraph text: " + aText);
      }

      // --- Change cell properties. ---
      xPropSet = (lo.beans.XPropertySet) xCell;
      // from styles.CharacterProperties
      xPropSet.setPropertyValue("CharColor", new uno.Any((Int32) 0x003399));
      xPropSet.setPropertyValue("CharHeight", new uno.Any((Single) 20.0));
      // from styles.ParagraphProperties
      xPropSet.setPropertyValue("ParaLeftMargin", new uno.Any((Int32) 500));
      // from table.CellProperties
      xPropSet.setPropertyValue("IsCellBackgroundTransparent", new uno.Any(false));
      xPropSet.setPropertyValue("CellBackColor", new uno.Any((Int32) 0x99CCFF));


      // --- Get cell address. ---
      lo.sheet.XCellAddressable xCellAddr = (lo.sheet.XCellAddressable) xCell;
      lo.table.CellAddress aAddress = xCellAddr.getCellAddress();
      aText = "Address of this cell:  Column=" + aAddress.Column;
      aText += ";  Row=" + aAddress.Row;
      aText += ";  Sheet=" + aAddress.Sheet;
      Console.WriteLine(aText);

      // --- Insert an annotation ---
      lo.sheet.XSheetAnnotationsSupplier xAnnotationsSupp = (lo.sheet.XSheetAnnotationsSupplier) xSheet;
      lo.sheet.XSheetAnnotations xAnnotations = xAnnotationsSupp.getAnnotations();
      xAnnotations.insertNew(aAddress, "This is an annotation");

      lo.sheet.XSheetAnnotationAnchor xAnnotAnchor = (lo.sheet.XSheetAnnotationAnchor) xCell;
      lo.sheet.XSheetAnnotation xAnnotation = xAnnotAnchor.getAnnotation();
      xAnnotation.setIsVisible(true);
    }

    // ________________________________________________________________

    /** All samples regarding the service com.sun.star.sheet.SheetCellRange. */
    private void doCellRangeSamples()
    {
      Console.WriteLine(
          "\n*** Samples for service sheet.SheetCellRange ***\n");
      lo.sheet.XSpreadsheet xSheet = getSpreadsheet(0);
      lo.table.XCellRange xCellRange = null;
      lo.beans.XPropertySet xPropSet = null;
      lo.table.CellRangeAddress aRangeAddress = null;

      // Preparation
      setFormula(xSheet, "B5", "First cell");
      setFormula(xSheet, "B6", "Second cell");
      // Get cell range B5:B6 by position - (column, row, column, row)
      xCellRange = xSheet.getCellRangeByPosition(1, 4, 1, 5);

      // --- Change cell range properties. ---
      xPropSet = (lo.beans.XPropertySet) xCellRange;
      // from com.sun.star.styles.CharacterProperties
      xPropSet.setPropertyValue(
          "CharColor", new uno.Any((Int32) 0x003399));
      xPropSet.setPropertyValue(
          "CharHeight", new uno.Any((Single) 20.0));
      // from com.sun.star.styles.ParagraphProperties
      xPropSet.setPropertyValue(
          "ParaLeftMargin", new uno.Any((Int32) 500));
      // from com.sun.star.table.CellProperties
      xPropSet.setPropertyValue(
          "IsCellBackgroundTransparent", new uno.Any(false));
      xPropSet.setPropertyValue(
          "CellBackColor", new uno.Any((Int32) 0x99CCFF));

      // --- Replace text in all cells. ---
      lo.util.XReplaceable xReplace =
            (lo.util.XReplaceable) xCellRange;
      lo.util.XReplaceDescriptor xReplaceDesc =
            xReplace.createReplaceDescriptor();
      xReplaceDesc.setSearchString("cell");
      xReplaceDesc.setReplaceString("text");
      // property SearchWords searches for whole cells!
      xReplaceDesc.setPropertyValue("SearchWords", new uno.Any(false));
      int nCount = xReplace.replaceAll(xReplaceDesc);
      Console.WriteLine("Search text replaced " + nCount + " times.");


      // --- Merge cells. ---
      xCellRange = xSheet.getCellRangeByName("F3:G6");
      prepareRange(xSheet, "E1:H7", "XMergeable");
      lo.util.XMergeable xMerge =
            (lo.util.XMergeable) xCellRange;
      xMerge.merge(true);


      // --- Change indentation. ---
      /* does not work (bug in XIndent implementation)
              prepareRange( xSheet, "I20:I23", "XIndent" );
              setValue( xSheet, "I21", 1 );
              setValue( xSheet, "I22", 1 );
              setValue( xSheet, "I23", 1 );

              xCellRange = xSheet.getCellRangeByName( "I21:I22" );
              lo.util.XIndent xIndent =
              (lo.util.XIndent) xCellRange;
              xIndent.incrementIndent();

              xCellRange = xSheet.getCellRangeByName( "I22:I23" );
              xIndent = (lo.util.XIndent) xCellRange;
              xIndent.incrementIndent();
      */


      // --- Column properties. ---
      xCellRange = xSheet.getCellRangeByName("B1");
      lo.table.XColumnRowRange xColRowRange =
            (lo.table.XColumnRowRange) xCellRange;
      lo.table.XTableColumns xColumns =
            xColRowRange.getColumns();

      uno.Any aColumnObj = xColumns.getByIndex(0);
      xPropSet = (lo.beans.XPropertySet) aColumnObj.Value;
      xPropSet.setPropertyValue("Width", new uno.Any((Int32) 6000));

      lo.container.XNamed xNamed =
            (lo.container.XNamed) aColumnObj.Value;
      Console.WriteLine(
          "The name of the wide column is " + xNamed.getName() + ".");


      // --- Cell range data ---
      prepareRange(xSheet, "A9:C30", "XCellRangeData");

      xCellRange = xSheet.getCellRangeByName("A10:C30");
      lo.sheet.XCellRangeData xData =
            (lo.sheet.XCellRangeData) xCellRange;
      uno.Any[][] aValues =
        {
            new uno.Any [] { new uno.Any( "Name" ),
                             new uno.Any( "Fruit" ),
                             new uno.Any( "Quantity" ) },
            new uno.Any [] { new uno.Any( "Alice" ),
                             new uno.Any( "Apples" ),
                             new uno.Any( (Double) 3.0 ) },
            new uno.Any [] { new uno.Any( "Alice" ),
                             new uno.Any( "Oranges" ),
                             new uno.Any( (Double) 7.0 ) },
            new uno.Any [] { new uno.Any( "Bob" ),
                             new uno.Any( "Apples" ),
                             new uno.Any( (Double) 3.0 ) },
            new uno.Any [] { new uno.Any( "Alice" ),
                             new uno.Any( "Apples" ),
                             new uno.Any( (Double) 9.0 ) },
            new uno.Any [] { new uno.Any( "Bob" ),
                             new uno.Any( "Apples" ),
                             new uno.Any( (Double) 5.0 ) },
            new uno.Any [] { new uno.Any( "Bob" ),
                             new uno.Any( "Oranges" ),
                             new uno.Any( (Double) 6.0 ) },
            new uno.Any [] { new uno.Any( "Alice" ),
                             new uno.Any( "Oranges" ),
                             new uno.Any( (Double) 3.0 ) },
            new uno.Any [] { new uno.Any( "Alice" ),
                             new uno.Any( "Apples" ),
                             new uno.Any( (Double) 8.0 ) },
            new uno.Any [] { new uno.Any( "Alice" ),
                             new uno.Any( "Oranges" ),
                             new uno.Any( (Double) 1.0 ) },
            new uno.Any [] { new uno.Any( "Bob" ),
                             new uno.Any( "Oranges" ),
                             new uno.Any( (Double) 2.0 ) },
            new uno.Any [] { new uno.Any( "Bob" ),
                             new uno.Any( "Oranges" ),
                             new uno.Any( (Double) 7.0 ) },
            new uno.Any [] { new uno.Any( "Bob" ),
                             new uno.Any( "Apples" ),
                             new uno.Any( (Double) 1.0 ) },
            new uno.Any [] { new uno.Any( "Alice" ),
                             new uno.Any( "Apples" ),
                             new uno.Any( (Double) 8.0 ) },
            new uno.Any [] { new uno.Any( "Alice" ),
                             new uno.Any( "Oranges" ),
                             new uno.Any( (Double) 8.0 ) },
            new uno.Any [] { new uno.Any( "Alice" ),
                             new uno.Any( "Apples" ),
                             new uno.Any( (Double) 7.0 ) },
            new uno.Any [] { new uno.Any( "Bob" ),
                             new uno.Any( "Apples" ),
                             new uno.Any( (Double) 1.0 ) },
            new uno.Any [] { new uno.Any( "Bob" ),
                             new uno.Any( "Oranges" ),
                             new uno.Any( (Double) 9.0 ) },
            new uno.Any [] { new uno.Any( "Bob" ),
                             new uno.Any( "Oranges" ),
                             new uno.Any( (Double) 3.0 ) },
            new uno.Any [] { new uno.Any( "Alice" ),
                             new uno.Any( "Oranges" ),
                             new uno.Any( (Double) 4.0 ) },
            new uno.Any [] { new uno.Any( "Alice" ),
                             new uno.Any( "Apples" ),
                             new uno.Any( (Double) 9.0 ) }
        };
      xData.setDataArray(aValues);


      // --- Get cell range address. ---
      lo.sheet.XCellRangeAddressable xRangeAddr =
            (lo.sheet.XCellRangeAddressable) xCellRange;
      aRangeAddress = xRangeAddr.getRangeAddress();
      Console.WriteLine(
          "Address of this range:  Sheet=" + aRangeAddress.Sheet);
      Console.WriteLine(
          "Start column=" + aRangeAddress.StartColumn + ";  Start row=" +
            aRangeAddress.StartRow);
      Console.WriteLine(
          "End column  =" + aRangeAddress.EndColumn   + ";  End row  =" +
            aRangeAddress.EndRow);


      // --- Sheet operation. ---
      // uses the range filled with XCellRangeData
      lo.sheet.XSheetOperation xSheetOp =
            (lo.sheet.XSheetOperation) xData;
      double fResult = xSheetOp.computeFunction(
          lo.sheet.GeneralFunction.AVERAGE);
      Console.WriteLine(
          "Average value of the data table A10:C30: " + fResult);


      // --- Fill series ---
      // Prepare the example
      setValue(xSheet, "E10", 1);
      setValue(xSheet, "E11", 4);
      setDate(xSheet, "E12", 30, 1, 2002);
      setFormula(xSheet, "I13", "Text 10");
      setFormula(xSheet, "E14", "Jan");
      setValue(xSheet, "K14", 10);
      setValue(xSheet, "E16", 1);
      setValue(xSheet, "F16", 2);
      setDate(xSheet, "E17", 28, 2, 2002);
      setDate(xSheet, "F17", 28, 1, 2002);
      setValue(xSheet, "E18", 6);
      setValue(xSheet, "F18", 4);

      lo.sheet.XCellSeries xSeries = null;
      // Fill 2 rows linear with end value
      // -> 2nd series is not filled completely
      xSeries = getCellSeries(xSheet, "E10:I11");
      xSeries.fillSeries(
          lo.sheet.FillDirection.TO_RIGHT,
          lo.sheet.FillMode.LINEAR,
          lo.sheet.FillDateMode.FILL_DATE_DAY, 2, 9);
      // Add months to a date
      xSeries = getCellSeries(xSheet, "E12:I12");
      xSeries.fillSeries(
          lo.sheet.FillDirection.TO_RIGHT,
          lo.sheet.FillMode.DATE,
          lo.sheet.FillDateMode.FILL_DATE_MONTH,
          1, 0x7FFFFFFF);
      // Fill right to left with a text containing a value
      xSeries = getCellSeries(xSheet, "E13:I13");
      xSeries.fillSeries(
          lo.sheet.FillDirection.TO_LEFT,
          lo.sheet.FillMode.LINEAR,
          lo.sheet.FillDateMode.FILL_DATE_DAY,
          10, 0x7FFFFFFF);
      // Fill with an user defined list
      xSeries = getCellSeries(xSheet, "E14:I14");
      xSeries.fillSeries(
          lo.sheet.FillDirection.TO_RIGHT,
          lo.sheet.FillMode.AUTO,
          lo.sheet.FillDateMode.FILL_DATE_DAY,
          1, 0x7FFFFFFF);
      // Fill bottom to top with a geometric series
      xSeries = getCellSeries(xSheet, "K10:K14");
      xSeries.fillSeries(
          lo.sheet.FillDirection.TO_TOP,
          lo.sheet.FillMode.GROWTH,
          lo.sheet.FillDateMode.FILL_DATE_DAY,
          2, 0x7FFFFFFF);
      // Auto fill
      xSeries = getCellSeries(xSheet, "E16:K18");
      xSeries.fillAuto(
          lo.sheet.FillDirection.TO_RIGHT, 2);
      // Fill series copies cell formats -> draw border here
      prepareRange(xSheet, "E9:K18", "XCellSeries");


      // --- Array formulas ---
      xCellRange = xSheet.getCellRangeByName("E21:G23");
      prepareRange(xSheet, "E20:G23", "XArrayFormulaRange");
      lo.sheet.XArrayFormulaRange xArrayFormula =
            (lo.sheet.XArrayFormulaRange) xCellRange;
      // Insert a 3x3 unit matrix.
      xArrayFormula.setArrayFormula("=A10:C12");
      Console.WriteLine(
          "Array formula is: " + xArrayFormula.getArrayFormula());


      // --- Multiple operations ---
      setFormula(xSheet, "E26", "=E27^F26");
      setValue(xSheet, "E27", 1);
      setValue(xSheet, "F26", 1);
      getCellSeries(xSheet, "E27:E31").fillAuto(
          lo.sheet.FillDirection.TO_BOTTOM, 1);
      getCellSeries(xSheet, "F26:J26").fillAuto(
          lo.sheet.FillDirection.TO_RIGHT, 1);
      setFormula(xSheet, "F33", "=SIN(E33)");
      setFormula(xSheet, "G33", "=COS(E33)");
      setFormula(xSheet, "H33", "=TAN(E33)");
      setValue(xSheet, "E34", 0);
      setValue(xSheet, "E35", 0.2);
      getCellSeries(xSheet, "E34:E38").fillAuto(
          lo.sheet.FillDirection.TO_BOTTOM, 2);
      prepareRange(xSheet, "E25:J38", "XMultipleOperation");

      lo.table.CellRangeAddress aFormulaRange =
            createCellRangeAddress(xSheet, "E26");
      lo.table.CellAddress aColCell =
            createCellAddress(xSheet, "E27");
      lo.table.CellAddress aRowCell =
            createCellAddress(xSheet, "F26");

      xCellRange = xSheet.getCellRangeByName("E26:J31");
      lo.sheet.XMultipleOperation xMultOp =
            (lo.sheet.XMultipleOperation) xCellRange;
      xMultOp.setTableOperation(
          aFormulaRange, lo.sheet.TableOperationMode.BOTH,
          aColCell, aRowCell);

      aFormulaRange = createCellRangeAddress(xSheet, "F33:H33");
      aColCell = createCellAddress(xSheet, "E33");
      // Row cell not needed

      xCellRange = xSheet.getCellRangeByName("E34:H38");
      xMultOp = (lo.sheet.XMultipleOperation) xCellRange;
      xMultOp.setTableOperation(
          aFormulaRange, lo.sheet.TableOperationMode.COLUMN,
          aColCell, aRowCell);


      // --- Cell Ranges Query ---
      xCellRange = xSheet.getCellRangeByName("A10:C30");
      lo.sheet.XCellRangesQuery xRangesQuery =
            (lo.sheet.XCellRangesQuery) xCellRange;
      lo.sheet.XSheetCellRanges xCellRanges =
            xRangesQuery.queryContentCells(
              (short) lo.sheet.CellFlags.STRING);
      Console.WriteLine(
          "Cells in A10:C30 containing text: "
            + xCellRanges.getRangeAddressesAsString());
    }

    /** Returns the XCellSeries interface of a cell range.
        @param xSheet  The spreadsheet containing the cell range.
        @param aRange  The address of the cell range.
        @return  The XCellSeries interface. */
    private lo.sheet.XCellSeries getCellSeries(
            lo.sheet.XSpreadsheet xSheet, String aRange)
    {
      return (lo.sheet.XCellSeries)
          xSheet.getCellRangeByName(aRange);
    }

    // ________________________________________________________________

    /** All samples regarding cell range collections. */
    private void doCellRangesSamples()
    {
      Console.WriteLine("\n*** Samples for cell range collections ***\n");

      // Create a new cell range container
      lo.lang.XMultiServiceFactory xDocFactory =
            (lo.lang.XMultiServiceFactory) getDocument();
      lo.sheet.XSheetCellRangeContainer xRangeCont =
            (lo.sheet.XSheetCellRangeContainer)
          xDocFactory.createInstance(
              "com.sun.star.sheet.SheetCellRanges");


      // --- Insert ranges ---
      insertRange(xRangeCont, 0, 0, 0, 0, 0, false);    // A1:A1
      insertRange(xRangeCont, 0, 0, 1, 0, 2, true);     // A2:A3
      insertRange(xRangeCont, 0, 1, 0, 1, 2, false);    // B1:B3


      // --- Query the list of filled cells ---
      Console.WriteLine("All filled cells: ");
      lo.container.XEnumerationAccess xCellsEA =
            xRangeCont.getCells();
      lo.container.XEnumeration xEnum =
            xCellsEA.createEnumeration();
      while( xEnum.hasMoreElements() )
      {
        uno.Any aCellObj = xEnum.nextElement();
        lo.sheet.XCellAddressable xAddr =
                (lo.sheet.XCellAddressable) aCellObj.Value;
        lo.table.CellAddress aAddr =
                xAddr.getCellAddress();
        Console.WriteLine(
            getCellAddressString(aAddr.Column, aAddr.Row) + " ");
      }
      Console.WriteLine();
    }

    /** Inserts a cell range address into a cell range container and prints
        a message.
        @param xContainer  lo.sheet.XSheetCellRangeContainer
                           interface of the container.
        @param nSheet  Index of sheet of the range.
        @param nStartCol  Index of first column of the range.
        @param nStartRow  Index of first row of the range.
        @param nEndCol  Index of last column of the range.
        @param nEndRow  Index of last row of the range.
        @param bMerge  Determines whether the new range should be merged
                       with the existing ranges.
    */
    private void insertRange(
            lo.sheet.XSheetCellRangeContainer xContainer,
            int nSheet, int nStartCol, int nStartRow, int nEndCol, int nEndRow,
            bool bMerge)
    {
      lo.table.CellRangeAddress aAddress =
            new lo.table.CellRangeAddress();
      aAddress.Sheet = (short) nSheet;
      aAddress.StartColumn = nStartCol;
      aAddress.StartRow = nStartRow;
      aAddress.EndColumn = nEndCol;
      aAddress.EndRow = nEndRow;
      xContainer.addRangeAddress(aAddress, bMerge);
      Console.WriteLine(
          "Inserting " + getCellRangeAddressString(aAddress)
            + " " + ( bMerge ? "   with" : "without" ) + " merge,"
            + " resulting list: " + xContainer.getRangeAddressesAsString());
    }

    // ________________________________________________________________

    /** All samples regarding cell cursors. */
    private void doCellCursorSamples()
    {
      Console.WriteLine("\n*** Samples for cell cursor ***\n");
      lo.sheet.XSpreadsheet xSheet = getSpreadsheet(0);


      // --- Find the array formula using a cell cursor ---
      lo.table.XCellRange xRange =
            xSheet.getCellRangeByName("F22");
      lo.sheet.XSheetCellRange xCellRange =
            (lo.sheet.XSheetCellRange) xRange;
      lo.sheet.XSheetCellCursor xCursor =
            xSheet.createCursorByRange(xCellRange);

      xCursor.collapseToCurrentArray();
      lo.sheet.XArrayFormulaRange xArray =
            (lo.sheet.XArrayFormulaRange) xCursor;
      Console.WriteLine(
          "Array formula in " + getCellRangeAddressString(xCursor, false)
            + " contains formula " + xArray.getArrayFormula());


      // --- Find the used area ---
      lo.sheet.XUsedAreaCursor xUsedCursor =
            (lo.sheet.XUsedAreaCursor) xCursor;
      xUsedCursor.gotoStartOfUsedArea(false);
      xUsedCursor.gotoEndOfUsedArea(true);
      // xUsedCursor and xCursor are interfaces of the same object -
      // so modifying xUsedCursor takes effect on xCursor:
      Console.WriteLine(
          "The used area is: " + getCellRangeAddressString(xCursor, true));
    }

    // ________________________________________________________________

    /** All samples regarding the formatting of cells and ranges. */
    private void doFormattingSamples()
    {
      Console.WriteLine("\n*** Formatting samples ***\n");
      lo.sheet.XSpreadsheet xSheet = getSpreadsheet(1);
      lo.table.XCellRange xCellRange;
      lo.beans.XPropertySet xPropSet = null;
      lo.container.XIndexAccess xRangeIA = null;
      lo.lang.XMultiServiceFactory xServiceManager;


      // --- Cell styles ---
      // get the cell style container
      lo.style.XStyleFamiliesSupplier xFamiliesSupplier =
            (lo.style.XStyleFamiliesSupplier) getDocument();
      lo.container.XNameAccess xFamiliesNA =
            xFamiliesSupplier.getStyleFamilies();
      uno.Any aCellStylesObj = xFamiliesNA.getByName("CellStyles");
      lo.container.XNameContainer xCellStylesNA =
            (lo.container.XNameContainer) aCellStylesObj.Value;

      // create a new cell style
      xServiceManager =
            (lo.lang.XMultiServiceFactory) getDocument();
      Object aCellStyle = xServiceManager.createInstance(
          "com.sun.star.style.CellStyle");
      String aStyleName = "MyNewCellStyle";
      xCellStylesNA.insertByName(
          aStyleName, new uno.Any(typeof(Object), aCellStyle));

      // modify properties of the new style
      xPropSet = (lo.beans.XPropertySet) aCellStyle;
      xPropSet.setPropertyValue(
          "CellBackColor", new uno.Any((Int32) 0x888888));
      xPropSet.setPropertyValue(
          "IsCellBackgroundTransparent", new uno.Any(false));



      // --- Query equal-formatted cell ranges ---
      // prepare example, use the new cell style
      xCellRange = xSheet.getCellRangeByName("D2:F2");
      xPropSet = (lo.beans.XPropertySet) xCellRange;
      xPropSet.setPropertyValue("CellStyle", new uno.Any(aStyleName));

      xCellRange = xSheet.getCellRangeByName("A3:G3");
      xPropSet = (lo.beans.XPropertySet) xCellRange;
      xPropSet.setPropertyValue("CellStyle", new uno.Any(aStyleName));

      // All ranges in one container
      xCellRange = xSheet.getCellRangeByName("A1:G3");
      Console.WriteLine("Service CellFormatRanges:");
      lo.sheet.XCellFormatRangesSupplier xFormatSupp =
            (lo.sheet.XCellFormatRangesSupplier) xCellRange;
      xRangeIA = xFormatSupp.getCellFormatRanges();
      Console.WriteLine(getCellRangeListString(xRangeIA));

      // Ranges sorted in SheetCellRanges containers
      Console.WriteLine("\nService UniqueCellFormatRanges:");
      lo.sheet.XUniqueCellFormatRangesSupplier
          xUniqueFormatSupp =
            (lo.sheet.XUniqueCellFormatRangesSupplier)
            xCellRange;
      lo.container.XIndexAccess xRangesIA =
            xUniqueFormatSupp.getUniqueCellFormatRanges();
      int nCount = xRangesIA.getCount();
      for( int nIndex = 0 ; nIndex < nCount ; ++nIndex )
      {
        uno.Any aRangesObj = xRangesIA.getByIndex(nIndex);
        xRangeIA =
                (lo.container.XIndexAccess) aRangesObj.Value;
        Console.WriteLine(
            "Container " + ( nIndex + 1 ) + ": " +
                getCellRangeListString(xRangeIA));
      }


      // --- Table auto formats ---
      // get the global collection of table auto formats,
      // use global service manager
      xServiceManager = getServiceManager();
      Object aAutoFormatsObj = xServiceManager.createInstance(
          "com.sun.star.sheet.TableAutoFormats");
      lo.container.XNameContainer xAutoFormatsNA =
            (lo.container.XNameContainer) aAutoFormatsObj;

      // create a new table auto format and insert into the container
      String aAutoFormatName =  "Temp_Example";
      bool bExistsAlready = xAutoFormatsNA.hasByName(aAutoFormatName);
      uno.Any aAutoFormatObj;
      if( bExistsAlready )
        // auto format already exists -> use it
        aAutoFormatObj = xAutoFormatsNA.getByName(aAutoFormatName);
      else
      {
        // create a new auto format (with document service manager!)
        xServiceManager =
                (lo.lang.XMultiServiceFactory) getDocument();
        aAutoFormatObj = new uno.Any(
            typeof(Object),
            xServiceManager.createInstance(
                "com.sun.star.sheet.TableAutoFormat"));
        xAutoFormatsNA.insertByName(aAutoFormatName, aAutoFormatObj);
      }
      // index access to the auto format fields
      lo.container.XIndexAccess xAutoFormatIA =
            (lo.container.XIndexAccess) aAutoFormatObj.Value;

      // set properties of all auto format fields
      for( int nRow = 0 ; nRow < 4 ; ++nRow )
      {
        int nRowColor = 0;
        switch( nRow )
        {
          case 0:
            nRowColor = 0x999999;
            break;
          case 1:
            nRowColor = 0xFFFFCC;
            break;
          case 2:
            nRowColor = 0xEEEEEE;
            break;
          case 3:
            nRowColor = 0x999999;
            break;
        }

        for( int nColumn = 0 ; nColumn < 4 ; ++nColumn )
        {
          int nColor = nRowColor;
          if( ( nColumn == 0 ) || ( nColumn == 3 ) )
            nColor -= 0x333300;

          // get the auto format field and apply properties
          uno.Any aFieldObj = xAutoFormatIA.getByIndex(
              4 * nRow + nColumn);
          xPropSet =
                    (lo.beans.XPropertySet) aFieldObj.Value;
          xPropSet.setPropertyValue(
              "CellBackColor", new uno.Any((Int32) nColor));
        }
      }

      // set the auto format to the spreadsheet
      xCellRange = xSheet.getCellRangeByName("A5:H25");
      lo.table.XAutoFormattable xAutoForm =
            (lo.table.XAutoFormattable) xCellRange;
      xAutoForm.autoFormat(aAutoFormatName);

      // remove the auto format
      if( !bExistsAlready )
        xAutoFormatsNA.removeByName(aAutoFormatName);


      // --- Conditional formats ---
      xSheet = getSpreadsheet(0);
      prepareRange(xSheet, "K20:K23", "Cond. Format");
      setValue(xSheet, "K21", 1);
      setValue(xSheet, "K22", 2);
      setValue(xSheet, "K23", 3);

      // get the conditional format object of the cell range
      xCellRange = xSheet.getCellRangeByName("K21:K23");
      xPropSet = (lo.beans.XPropertySet) xCellRange;
      lo.sheet.XSheetConditionalEntries xEntries =
            (lo.sheet.XSheetConditionalEntries)
            xPropSet.getPropertyValue("ConditionalFormat").Value;

      // create a condition and apply it to the range
      lo.beans.PropertyValue[] aCondition =
            new lo.beans.PropertyValue[3];
      aCondition[0] = new lo.beans.PropertyValue();
      aCondition[0].Name  = "Operator";
      aCondition[0].Value =
            new uno.Any(
              typeof(lo.sheet.ConditionOperator),
              lo.sheet.ConditionOperator.GREATER);
      aCondition[1] = new lo.beans.PropertyValue();
      aCondition[1].Name  = "Formula1";
      aCondition[1].Value = new uno.Any("1");
      aCondition[2] = new lo.beans.PropertyValue();
      aCondition[2].Name  = "StyleName";
      aCondition[2].Value = new uno.Any(aStyleName);
      xEntries.addNew(aCondition);
      xPropSet.setPropertyValue(
          "ConditionalFormat",
          new uno.Any(
              typeof(lo.sheet.XSheetConditionalEntries),
              xEntries));
    }

    // ________________________________________________________________

    /** All samples regarding the spreadsheet document. */
    private void doDocumentSamples()
    {
      Console.WriteLine("\n*** Samples for spreadsheet document ***\n");


      // --- Insert a new spreadsheet ---
      lo.sheet.XSpreadsheet xSheet =
            insertSpreadsheet("A new sheet", (short) 0x7FFF);


      // --- Copy a cell range ---
      prepareRange(xSheet, "A1:B3", "Copy from");
      prepareRange(xSheet, "D1:E3", "To");
      setValue(xSheet, "A2", 123);
      setValue(xSheet, "B2", 345);
      setFormula(xSheet, "A3", "=SUM(A2:B2)");
      setFormula(xSheet, "B3", "=FORMULA(A3)");

      lo.sheet.XCellRangeMovement xMovement =
            (lo.sheet.XCellRangeMovement) xSheet;
      lo.table.CellRangeAddress aSourceRange =
            createCellRangeAddress(xSheet, "A2:B3");
      lo.table.CellAddress aDestCell =
            createCellAddress(xSheet, "D2");
      xMovement.copyRange(aDestCell, aSourceRange);


      // --- Print automatic column page breaks ---
      lo.sheet.XSheetPageBreak xPageBreak =
            (lo.sheet.XSheetPageBreak) xSheet;
      lo.sheet.TablePageBreakData[] aPageBreakArray =
            xPageBreak.getColumnPageBreaks();

      Console.Write("Automatic column page breaks:");
      for( int nIndex = 0 ; nIndex < aPageBreakArray.Length ; ++nIndex )
        if( !aPageBreakArray[nIndex].ManualBreak )
          Console.Write(" " + aPageBreakArray[nIndex].Position);
      Console.WriteLine();


      // --- Document properties ---
      lo.beans.XPropertySet xPropSet =
            (lo.beans.XPropertySet) getDocument();

      String aText = "Value of property IsIterationEnabled: ";
      aText +=
            (Boolean) xPropSet.getPropertyValue("IsIterationEnabled").Value;
      Console.WriteLine(aText);
      aText = "Value of property IterationCount: ";
      aText += (Int32) xPropSet.getPropertyValue("IterationCount").Value;
      Console.WriteLine(aText);
      aText = "Value of property NullDate: ";
      lo.util.Date aDate = (lo.util.Date)
          xPropSet.getPropertyValue("NullDate").Value;
      aText += aDate.Year + "-" + aDate.Month + "-" + aDate.Day;
      Console.WriteLine(aText);


      // --- Data validation ---
      prepareRange(xSheet, "A5:C7", "Validation");
      setFormula(xSheet, "A6", "Insert values between 0.0 and 5.0 below:");

      lo.table.XCellRange xCellRange =
            xSheet.getCellRangeByName("A7:C7");
      lo.beans.XPropertySet xCellPropSet =
            (lo.beans.XPropertySet) xCellRange;
      // validation properties
      lo.beans.XPropertySet xValidPropSet =
            (lo.beans.XPropertySet)
            xCellPropSet.getPropertyValue("Validation").Value;
      xValidPropSet.setPropertyValue(
          "Type",
          new uno.Any(
              typeof(lo.sheet.ValidationType),
              lo.sheet.ValidationType.DECIMAL));
      xValidPropSet.setPropertyValue(
          "ShowErrorMessage", new uno.Any(true));
      xValidPropSet.setPropertyValue(
          "ErrorMessage", new uno.Any("This is an invalid value!"));
      xValidPropSet.setPropertyValue(
          "ErrorAlertStyle",
          new uno.Any(
              typeof(lo.sheet.ValidationAlertStyle),
              lo.sheet.ValidationAlertStyle.STOP));
      // condition
      lo.sheet.XSheetCondition xCondition =
            (lo.sheet.XSheetCondition) xValidPropSet;
      xCondition.setOperator(
          lo.sheet.ConditionOperator.BETWEEN);
      xCondition.setFormula1("0.0");
      xCondition.setFormula2("5.0");
      // apply on cell range
      xCellPropSet.setPropertyValue(
          "Validation",
          new uno.Any(
              typeof(lo.beans.XPropertySet),
              xValidPropSet));


      // --- Scenarios ---
      uno.Any[][] aValues = {
            new uno.Any [] { uno.Any.VOID, uno.Any.VOID },
            new uno.Any [] { uno.Any.VOID, uno.Any.VOID }
        };

      aValues[0][0] = new uno.Any((Double) 11);
      aValues[0][1] = new uno.Any((Double) 12);
      aValues[1][0] = new uno.Any("Test13");
      aValues[1][1] = new uno.Any("Test14");
      insertScenario(
          xSheet, "B10:C11", aValues,
          "First Scenario", "The first scenario.");

      aValues[0][0] = new uno.Any("Test21");
      aValues[0][1] = new uno.Any("Test22");
      aValues[1][0] = new uno.Any((Double) 23);
      aValues[1][1] = new uno.Any((Double) 24);
      insertScenario(
          xSheet, "B10:C11", aValues,
          "Second Scenario", "The visible scenario.");

      aValues[0][0] = new uno.Any((Double) 31);
      aValues[0][1] = new uno.Any((Double) 32);
      aValues[1][0] = new uno.Any("Test33");
      aValues[1][1] = new uno.Any("Test34");
      insertScenario(
          xSheet, "B10:C11", aValues,
          "Third Scenario", "The last scenario.");

      // show second scenario
      showScenario(xSheet, "Second Scenario");
    }

    /** Inserts a scenario containing one cell range into a sheet and
        applies the value array.
        @param xSheet           The XSpreadsheet interface of the spreadsheet.
        @param aRange           The range address for the scenario.
        @param aValueArray      The array of cell contents.
        @param aScenarioName    The name of the new scenario.
        @param aScenarioComment The user comment for the scenario. */
    private void insertScenario(
            lo.sheet.XSpreadsheet xSheet,
            String aRange,
            uno.Any[][] aValueArray,
            String aScenarioName,
            String aScenarioComment)
    {
      // get the cell range with the given address
      lo.table.XCellRange xCellRange =
            xSheet.getCellRangeByName(aRange);

      // create the range address sequence
      lo.sheet.XCellRangeAddressable xAddr =
            (lo.sheet.XCellRangeAddressable) xCellRange;
      lo.table.CellRangeAddress[] aRangesSeq =
            new lo.table.CellRangeAddress[1];
      aRangesSeq[0] = xAddr.getRangeAddress();

      // create the scenario
      lo.sheet.XScenariosSupplier xScenSupp =
            (lo.sheet.XScenariosSupplier) xSheet;
      lo.sheet.XScenarios xScenarios =
            xScenSupp.getScenarios();
      xScenarios.addNewByName(aScenarioName, aRangesSeq, aScenarioComment);

      // insert the values into the range
      lo.sheet.XCellRangeData xData =
            (lo.sheet.XCellRangeData) xCellRange;
      xData.setDataArray(aValueArray);
    }

    /** Activates a scenario.
        @param xSheet           The XSpreadsheet interface of the spreadsheet.
        @param aScenarioName    The name of the scenario. */
    private void showScenario(
            lo.sheet.XSpreadsheet xSheet,
            String aScenarioName)
    {
      // get the scenario set
      lo.sheet.XScenariosSupplier xScenSupp =
            (lo.sheet.XScenariosSupplier) xSheet;
      lo.sheet.XScenarios xScenarios =
            xScenSupp.getScenarios();

      // get the scenario and activate it
      uno.Any aScenarioObj = xScenarios.getByName(aScenarioName);
      lo.sheet.XScenario xScenario =
            (lo.sheet.XScenario) aScenarioObj.Value;
      xScenario.apply();
    }

    // ________________________________________________________________

    private void doNamedRangesSamples()
    {
      Console.WriteLine("\n*** Samples for named ranges ***\n");
      lo.sheet.XSpreadsheetDocument xDocument =
            getDocument();
      lo.sheet.XSpreadsheet xSheet =
            getSpreadsheet(0);


      // --- Named ranges ---
      prepareRange(xSheet, "G42:H45", "Named ranges");
      xSheet.getCellByPosition(6, 42).setValue(1);
      xSheet.getCellByPosition(6, 43).setValue(2);
      xSheet.getCellByPosition(7, 42).setValue(3);
      xSheet.getCellByPosition(7, 43).setValue(4);

      // insert a named range
      lo.beans.XPropertySet xDocProp =
            (lo.beans.XPropertySet) xDocument;
      uno.Any aRangesObj = xDocProp.getPropertyValue("NamedRanges");
      lo.sheet.XNamedRanges xNamedRanges =
            (lo.sheet.XNamedRanges) aRangesObj.Value;
      lo.table.CellAddress aRefPos =
            new lo.table.CellAddress();
      aRefPos.Sheet  = 0;
      aRefPos.Column = 6;
      aRefPos.Row    = 44;
      xNamedRanges.addNewByName("ExampleName", "SUM(G43:G44)", aRefPos, 0);

      // use the named range in formulas
      xSheet.getCellByPosition(6, 44).setFormula("=ExampleName");
      xSheet.getCellByPosition(7, 44).setFormula("=ExampleName");


      // --- Label ranges ---
      prepareRange(xSheet, "G47:I50", "Label ranges");
      lo.table.XCellRange xRange =
            xSheet.getCellRangeByPosition(6, 47, 7, 49);
      lo.sheet.XCellRangeData xData =
            (lo.sheet.XCellRangeData) xRange;
      uno.Any[][] aValues =
        {
            new uno.Any [] { new uno.Any( "Apples" ),
                             new uno.Any( "Oranges" ) },
            new uno.Any [] { new uno.Any( (Double) 5 ),
                             new uno.Any( (Double) 7 ) },
            new uno.Any [] { new uno.Any( (Double) 6 ),
                             new uno.Any( (Double) 8 ) }
        };
      xData.setDataArray(aValues);

      // insert a column label range
      uno.Any aLabelsObj = xDocProp.getPropertyValue("ColumnLabelRanges");
      lo.sheet.XLabelRanges xLabelRanges =
            (lo.sheet.XLabelRanges) aLabelsObj.Value;
      lo.table.CellRangeAddress aLabelArea =
            new lo.table.CellRangeAddress();
      aLabelArea.Sheet       = 0;
      aLabelArea.StartColumn = 6;
      aLabelArea.StartRow    = 47;
      aLabelArea.EndColumn   = 7;
      aLabelArea.EndRow      = 47;
      lo.table.CellRangeAddress aDataArea =
            new lo.table.CellRangeAddress();
      aDataArea.Sheet       = 0;
      aDataArea.StartColumn = 6;
      aDataArea.StartRow    = 48;
      aDataArea.EndColumn   = 7;
      aDataArea.EndRow      = 49;
      xLabelRanges.addNew(aLabelArea, aDataArea);

      // use the label range in formulas
      xSheet.getCellByPosition(8, 48).setFormula("=Apples+Oranges");
      xSheet.getCellByPosition(8, 49).setFormula("=Apples+Oranges");
    }

    // ________________________________________________________________

    /** Helper for doDatabaseSamples: get name of first database. */
    private String getFirstDatabaseName()
    {
      String aDatabase = null;
      lo.lang.XMultiServiceFactory xServiceManager =
            getServiceManager();
      lo.container.XNameAccess xContext =
            (lo.container.XNameAccess)
          xServiceManager.createInstance(
              "com.sun.star.sdb.DatabaseContext");
      String[] aNames = xContext.getElementNames();
      if( aNames.Length > 0 )
        aDatabase = aNames[0];
      return aDatabase;
    }

    /** Helper for doDatabaseSamples: get name of first table in a database. */
    private String getFirstTableName(String aDatabase)
    {
      if( aDatabase == null )
        return null;

      String aTable = null;
      lo.lang.XMultiServiceFactory xServiceManager =
            getServiceManager();
      lo.container.XNameAccess xContext =
            (lo.container.XNameAccess)
          xServiceManager.createInstance(
              "com.sun.star.sdb.DatabaseContext");
      lo.sdb.XCompletedConnection xSource =
            (lo.sdb.XCompletedConnection)
            xContext.getByName(aDatabase).Value;
      lo.task.XInteractionHandler xHandler =
            (lo.task.XInteractionHandler)
            xServiceManager.createInstance(
                "com.sun.star.task.InteractionHandler");
      lo.sdbcx.XTablesSupplier xSupplier =
            (lo.sdbcx.XTablesSupplier)
           xSource.connectWithCompletion(xHandler);
      lo.container.XNameAccess xTables =
            xSupplier.getTables();
      String[] aNames = xTables.getElementNames();
      if( aNames.Length > 0 )
        aTable = aNames[0];
      return aTable;
    }

    private void doDatabaseSamples()
    {
      Console.WriteLine("\n*** Samples for database operations ***\n");
      lo.sheet.XSpreadsheet xSheet = getSpreadsheet(2);


      // --- put some example data into the sheet ---
      lo.table.XCellRange xRange =
            xSheet.getCellRangeByName("B3:D24");
      lo.sheet.XCellRangeData xData =
            (lo.sheet.XCellRangeData) xRange;
      uno.Any[][] aValues =
        {
            new uno.Any [] { new uno.Any( "Name" ),
                             new uno.Any( "Year" ),
                             new uno.Any( "Sales" ) },
            new uno.Any [] { new uno.Any( "Alice" ),
                             new uno.Any( (Double) 2001 ),
                             new uno.Any( (Double) 4.0 ) },
            new uno.Any [] { new uno.Any( "Carol" ),
                             new uno.Any( (Double) 1997 ),
                             new uno.Any( (Double) 3.0 ) },
            new uno.Any [] { new uno.Any( "Carol" ),
                             new uno.Any( (Double) 1998 ),
                             new uno.Any( (Double) 8.0 ) },
            new uno.Any [] { new uno.Any( "Bob" ),
                             new uno.Any( (Double) 1997 ),
                             new uno.Any( (Double) 8.0 ) },
            new uno.Any [] { new uno.Any( "Alice" ),
                             new uno.Any( (Double) 2002 ),
                             new uno.Any( (Double) 9.0 ) },
            new uno.Any [] { new uno.Any( "Alice" ),
                             new uno.Any( (Double) 1999 ),
                             new uno.Any( (Double) 7.0 ) },
            new uno.Any [] { new uno.Any( "Alice" ),
                             new uno.Any( (Double) 1996 ),
                             new uno.Any( (Double) 3.0 ) },
            new uno.Any [] { new uno.Any( "Bob" ),
                             new uno.Any( (Double) 2000 ),
                             new uno.Any( (Double) 1.0 ) },
            new uno.Any [] { new uno.Any( "Carol" ),
                             new uno.Any( (Double) 1999 ),
                             new uno.Any( (Double) 5.0 ) },
            new uno.Any [] { new uno.Any( "Bob" ),
                             new uno.Any( (Double) 2002 ),
                             new uno.Any( (Double) 1.0 ) },
            new uno.Any [] { new uno.Any( "Carol" ),
              new uno.Any( (Double) 2001 ),
              new uno.Any( (Double) 5.0 ) },
            new uno.Any [] { new uno.Any( "Carol" ),
              new uno.Any( (Double) 2000 ),
              new uno.Any( (Double) 1.0 ) },
            new uno.Any [] { new uno.Any( "Carol" ),
              new uno.Any( (Double) 1996 ),
              new uno.Any( (Double) 8.0 ) },
            new uno.Any [] { new uno.Any( "Bob" ),
              new uno.Any( (Double) 1996 ),
              new uno.Any( (Double) 7.0 ) },
            new uno.Any [] { new uno.Any( "Alice" ),
              new uno.Any( (Double) 1997 ),
              new uno.Any( (Double) 3.0 ) },
            new uno.Any [] { new uno.Any( "Alice" ),
              new uno.Any( (Double) 2000 ),
              new uno.Any( (Double) 9.0 ) },
            new uno.Any [] { new uno.Any( "Bob" ),
              new uno.Any( (Double) 1998 ),
              new uno.Any( (Double) 1.0 ) },
            new uno.Any [] { new uno.Any( "Bob" ),
              new uno.Any( (Double) 1999 ),
              new uno.Any( (Double) 6.0 ) },
            new uno.Any [] { new uno.Any( "Carol" ),
              new uno.Any( (Double) 2002 ),
              new uno.Any( (Double) 8.0 ) },
            new uno.Any [] { new uno.Any( "Alice" ),
              new uno.Any( (Double) 1998 ),
              new uno.Any( (Double) 5.0 ) },
            new uno.Any [] { new uno.Any( "Bob" ),
              new uno.Any( (Double) 2001 ),
              new uno.Any( (Double) 6.0 ) }
        };
      xData.setDataArray(aValues);


      // --- filter for second column >= 1998 ---
      lo.sheet.XSheetFilterable xFilter =
            (lo.sheet.XSheetFilterable) xRange;
      lo.sheet.XSheetFilterDescriptor xFilterDesc =
            xFilter.createFilterDescriptor(true);
      lo.sheet.TableFilterField[] aFilterFields =
            new lo.sheet.TableFilterField[1];
      aFilterFields[0] = new lo.sheet.TableFilterField();
      aFilterFields[0].Field        = 1;
      aFilterFields[0].IsNumeric    = true;
      aFilterFields[0].Operator =
            lo.sheet.FilterOperator.GREATER_EQUAL;
      aFilterFields[0].NumericValue = 1998;
      xFilterDesc.setFilterFields(aFilterFields);
      lo.beans.XPropertySet xFilterProp =
            (lo.beans.XPropertySet) xFilterDesc;
      xFilterProp.setPropertyValue(
          "ContainsHeader", new uno.Any(true));
      xFilter.filter(xFilterDesc);


      // --- do the same filter as above, using criteria from a cell range ---
      lo.table.XCellRange xCritRange =
            xSheet.getCellRangeByName("B27:B28");
      lo.sheet.XCellRangeData xCritData =
            (lo.sheet.XCellRangeData) xCritRange;
      uno.Any[][] aCritValues =
        {
            new uno.Any [] { new uno.Any( "Year" ) },
            new uno.Any [] { new uno.Any( ">= 1998" ) }
        };
      xCritData.setDataArray(aCritValues);
      lo.sheet.XSheetFilterableEx xCriteria =
            (lo.sheet.XSheetFilterableEx) xCritRange;
      xFilterDesc = xCriteria.createFilterDescriptorByObject(xFilter);
      if( xFilterDesc != null )
        xFilter.filter(xFilterDesc);


      // --- sort by second column, ascending ---
      lo.util.SortField[] aSortFields =
            new lo.util.SortField[1];
      aSortFields[0] = new lo.util.SortField();
      aSortFields[0].Field         = 1;
      aSortFields[0].SortAscending = true;

      lo.beans.PropertyValue[] aSortDesc =
            new lo.beans.PropertyValue[2];
      aSortDesc[0] = new lo.beans.PropertyValue();
      aSortDesc[0].Name   = "SortFields";
      aSortDesc[0].Value  =
            new uno.Any(
              typeof(lo.util.SortField[]),
              aSortFields);
      aSortDesc[1] = new lo.beans.PropertyValue();
      aSortDesc[1].Name   = "ContainsHeader";
      aSortDesc[1].Value  = new uno.Any(true);

      lo.util.XSortable xSort =
            (lo.util.XSortable) xRange;
      xSort.sort(aSortDesc);


      // --- insert subtotals ---
      lo.sheet.XSubTotalCalculatable xSub =
            (lo.sheet.XSubTotalCalculatable) xRange;
      lo.sheet.XSubTotalDescriptor xSubDesc =
            xSub.createSubTotalDescriptor(true);
      lo.sheet.SubTotalColumn[] aColumns =
            new lo.sheet.SubTotalColumn[1];
      // calculate sum of third column
      aColumns[0] = new lo.sheet.SubTotalColumn();
      aColumns[0].Column   = 2;
      aColumns[0].Function = lo.sheet.GeneralFunction.SUM;
      // group by first column
      xSubDesc.addNew(aColumns, 0);
      xSub.applySubTotals(xSubDesc, true);

      String aDatabase = getFirstDatabaseName();
      String aTableName = getFirstTableName(aDatabase);
      if( aDatabase != null && aTableName != null )
      {
        // --- import from database ---
        lo.beans.PropertyValue[] aImportDesc =
                new lo.beans.PropertyValue[3];
        aImportDesc[0] = new lo.beans.PropertyValue();
        aImportDesc[0].Name     = "DatabaseName";
        aImportDesc[0].Value    = new uno.Any(aDatabase);
        aImportDesc[1] = new lo.beans.PropertyValue();
        aImportDesc[1].Name     = "SourceType";
        aImportDesc[1].Value    =
                new uno.Any(
                typeof(lo.sheet.DataImportMode),
                lo.sheet.DataImportMode.TABLE);
        aImportDesc[2] = new lo.beans.PropertyValue();
        aImportDesc[2].Name     = "SourceObject";
        aImportDesc[2].Value    = new uno.Any(aTableName);

        lo.table.XCellRange xImportRange =
                xSheet.getCellRangeByName("B35:B35");
        lo.util.XImportable xImport =
                (lo.util.XImportable) xImportRange;
        xImport.doImport(aImportDesc);


        // --- use the temporary database range to find the
        // imported data's size ---
        lo.beans.XPropertySet xDocProp =
                (lo.beans.XPropertySet) getDocument();
        uno.Any aRangesObj = xDocProp.getPropertyValue("DatabaseRanges");
        lo.container.XNameAccess xRanges =
                (lo.container.XNameAccess) aRangesObj.Value;
        String[] aNames = xRanges.getElementNames();
        for( int i=0 ; i<aNames.Length ; i++ )
        {
          uno.Any aRangeObj = xRanges.getByName(aNames[i]);
          lo.beans.XPropertySet xRangeProp =
                    (lo.beans.XPropertySet) aRangeObj.Value;
          bool bUser = (Boolean)
              xRangeProp.getPropertyValue("IsUserDefined").Value;
          if( !bUser )
          {
            // this is the temporary database range -
            // get the cell range and format it
            lo.sheet.XCellRangeReferrer xRef =
                        (lo.sheet.XCellRangeReferrer)
                  aRangeObj.Value;
            lo.table.XCellRange xResultRange =
                        xRef.getReferredCells();
            lo.beans.XPropertySet xResultProp =
                        (lo.beans.XPropertySet) xResultRange;
            xResultProp.setPropertyValue(
                "IsCellBackgroundTransparent", new uno.Any(false));
            xResultProp.setPropertyValue(
                "CellBackColor", new uno.Any((Int32) 0xFFFFCC));
          }
        }
      }
      else
        Console.WriteLine("can't get database");
    }

    // ________________________________________________________________

    private void doDataPilotSamples()
    {
      Console.WriteLine("\n*** Samples for Data Pilot ***\n");
      lo.sheet.XSpreadsheet xSheet = getSpreadsheet(0);


      // --- Create a new DataPilot table ---
      prepareRange(xSheet, "A38:C38", "Data Pilot");
      lo.sheet.XDataPilotTablesSupplier xDPSupp =
            (lo.sheet.XDataPilotTablesSupplier) xSheet;
      lo.sheet.XDataPilotTables xDPTables =
            xDPSupp.getDataPilotTables();
      lo.sheet.XDataPilotDescriptor xDPDesc =
            xDPTables.createDataPilotDescriptor();
      // set source range (use data range from CellRange test)
      lo.table.CellRangeAddress aSourceAddress =
            createCellRangeAddress(xSheet, "A10:C30");
      xDPDesc.setSourceRange(aSourceAddress);
      // settings for fields
      lo.container.XIndexAccess xFields =
            xDPDesc.getDataPilotFields();
      uno.Any aFieldObj;
      lo.beans.XPropertySet xFieldProp;
      // use first column as column field
      aFieldObj = xFields.getByIndex(0);
      xFieldProp = (lo.beans.XPropertySet) aFieldObj.Value;
      xFieldProp.setPropertyValue(
          "Orientation",
          new uno.Any(
              typeof(lo.sheet.DataPilotFieldOrientation),
              lo.sheet.DataPilotFieldOrientation.COLUMN));
      // use second column as row field
      aFieldObj = xFields.getByIndex(1);
      xFieldProp = (lo.beans.XPropertySet) aFieldObj.Value;
      xFieldProp.setPropertyValue(
          "Orientation",
          new uno.Any(
              typeof(lo.sheet.DataPilotFieldOrientation),
              lo.sheet.DataPilotFieldOrientation.ROW));
      // use third column as data field, calculating the sum
      aFieldObj = xFields.getByIndex(2);
      xFieldProp = (lo.beans.XPropertySet) aFieldObj.Value;
      xFieldProp.setPropertyValue(
          "Orientation",
          new uno.Any(
              typeof(lo.sheet.DataPilotFieldOrientation),
              lo.sheet.DataPilotFieldOrientation.DATA));
      xFieldProp.setPropertyValue(
          "Function",
          new uno.Any(
              typeof(lo.sheet.GeneralFunction),
              lo.sheet.GeneralFunction.SUM));
      // select output position
      lo.table.CellAddress aDestAddress =
            createCellAddress(xSheet, "A40");
      xDPTables.insertNewByName("DataPilotExample", aDestAddress, xDPDesc);


      // --- Modify the DataPilot table ---
      uno.Any aDPTableObj = xDPTables.getByName("DataPilotExample");
      xDPDesc =
            (lo.sheet.XDataPilotDescriptor) aDPTableObj.Value;
      xFields = xDPDesc.getDataPilotFields();
      // add a second data field from the third column,
      // calculating the average
      aFieldObj = xFields.getByIndex(2);
      xFieldProp = (lo.beans.XPropertySet) aFieldObj.Value;
      xFieldProp.setPropertyValue(
          "Orientation",
          new uno.Any(
              typeof(lo.sheet.DataPilotFieldOrientation),
              lo.sheet.DataPilotFieldOrientation.DATA));
      xFieldProp.setPropertyValue(
          "Function",
          new uno.Any(
              typeof(lo.sheet.GeneralFunction),
              lo.sheet.GeneralFunction.AVERAGE));
    }

    // ________________________________________________________________

    private void doFunctionAccessSamples()
    {
      Console.WriteLine("\n*** Samples for function handling ***\n");
      lo.lang.XMultiServiceFactory xServiceManager =
            getServiceManager();


      // --- Calculate a function ---
      Object aFuncInst = xServiceManager.createInstance(
          "com.sun.star.sheet.FunctionAccess");
      lo.sheet.XFunctionAccess xFuncAcc =
            (lo.sheet.XFunctionAccess) aFuncInst;
      // put the data in a two-dimensional array
      Double[][] aData = { new Double[] { 1.0, 2.0, 3.0 } };
      // construct the array of function arguments
      uno.Any[] aArgs = new uno.Any[2];
      aArgs[0] = new uno.Any(typeof(Double[][]), aData);
      aArgs[1] = new uno.Any((Double) 2.0);
      uno.Any aResult = xFuncAcc.callFunction("ZTEST", aArgs);
      Console.WriteLine(
          "ZTEST result for data {1,2,3} and value 2 is " + aResult.Value);


      // --- Get the list of recently used functions ---
      Object aRecInst = xServiceManager.createInstance(
          "com.sun.star.sheet.RecentFunctions");
      lo.sheet.XRecentFunctions xRecFunc =
            (lo.sheet.XRecentFunctions) aRecInst;
      int[] nRecentIds = xRecFunc.getRecentFunctionIds();


      // --- Get the names for these functions ---
      Object aDescInst = xServiceManager.createInstance(
          "com.sun.star.sheet.FunctionDescriptions");
      lo.sheet.XFunctionDescriptions xFuncDesc =
            (lo.sheet.XFunctionDescriptions) aDescInst;
      Console.Write("Recently used functions: ");
      for( int nFunction=0 ; nFunction<nRecentIds.Length ; nFunction++ )
      {
        lo.beans.PropertyValue[] aProperties =
                xFuncDesc.getById(nRecentIds[nFunction]);
        for( int nProp=0 ; nProp<aProperties.Length ; nProp++ )
          if( aProperties[nProp].Name.Equals("Name") )
            Console.Write(aProperties[nProp].Value + " ");
      }
      Console.WriteLine();
    }

    // ________________________________________________________________

    private void doApplicationSettingsSamples()
    {
      Console.WriteLine("\n*** Samples for application settings ***\n");
      lo.lang.XMultiServiceFactory xServiceManager =
            getServiceManager();


      // --- Get the user defined sort lists ---
      Object aSettings = xServiceManager.createInstance(
          "com.sun.star.sheet.GlobalSheetSettings");
      lo.beans.XPropertySet xPropSet =
            (lo.beans.XPropertySet) aSettings;
      String[] aEntries = (String[])
          xPropSet.getPropertyValue("UserLists").Value;
      Console.WriteLine("User defined sort lists:");
      for( int i=0 ; i<aEntries.Length ; i++ )
        Console.WriteLine(aEntries[i]);
    }

    // ________________________________________________________________

  }
}
